const db = require('./db')

const character_comment = {
    async create({ characterId, userId, content }) {
        const [result] = await db.query(
            `INSERT INTO character_comment (character_id, user_id, content) 
           VALUES (?, ?, ?)`,
            [characterId, userId, content]
        );
        return this.findById(result.insertId);
    },

    async findById(id) {
        const [rows] = await db.query(
            `SELECT * FROM character_comment WHERE id = ?`,
            [id]
        );
        return rows[0];
    },

    async findByCharacterId(characterId, page = 1, limit = 10) {
        const offset = (page - 1) * limit;
        const [rows] = await db.query(
            `SELECT 
            c.*, 
            u.username AS user_name
        FROM character_comment c
        LEFT JOIN users u ON c.user_id = u.user_id
        WHERE c.character_id = ?
        ORDER BY c.created_at DESC
        LIMIT ? OFFSET ?`,
            [characterId, limit, offset]
        );
        return rows;
    },

    async updateContent(id, newContent) {
        await db.query(
            `UPDATE character_comment SET content = ? WHERE id = ?`,
            [newContent, id]
        );
        return this.findById(id);
    },

    async delete(id) {
        await db.query(
            `DELETE FROM character_comment WHERE id = ?`,
            [id]
        );
        return true;
    },

    async countByCharacterId(characterId) {
        const [rows] = await db.query(
            `SELECT COUNT(*) AS total FROM character_comment 
       WHERE character_id = ?`,
            [characterId]
        );
        return rows[0].total;
    }
}

module.exports = character_comment;